
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/29/2011 03:01:12
-- Generated from EDMX file: D:\YeapSvn\trunk\Yeap\Kernel\Storage\YeapModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [YeapDb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_User_HomePageLayout]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_User_HomePageLayout];
GO
IF OBJECT_ID(N'[dbo].[FK_UserWidget_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserWidget] DROP CONSTRAINT [FK_UserWidget_User];
GO
IF OBJECT_ID(N'[dbo].[FK_UserWidget_Widget]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserWidget] DROP CONSTRAINT [FK_UserWidget_Widget];
GO
IF OBJECT_ID(N'[dbo].[FK_Work_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Work] DROP CONSTRAINT [FK_Work_User];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[HomePageLayout]', 'U') IS NOT NULL
    DROP TABLE [dbo].[HomePageLayout];
GO
IF OBJECT_ID(N'[dbo].[User]', 'U') IS NOT NULL
    DROP TABLE [dbo].[User];
GO
IF OBJECT_ID(N'[dbo].[UserWidget]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserWidget];
GO
IF OBJECT_ID(N'[dbo].[Widget]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Widget];
GO
IF OBJECT_ID(N'[dbo].[Work]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Work];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'HomePageLayout'
CREATE TABLE [dbo].[HomePageLayout] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [LayoutRecord] nvarchar(20)  NOT NULL,
    [LayoutImage] nvarchar(70)  NOT NULL
);
GO

-- Creating table 'User'
CREATE TABLE [dbo].[User] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(50)  NOT NULL,
    [Password] nvarchar(50)  NOT NULL,
    [Salt] nvarchar(50)  NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [PasswordModificationDate] datetime  NULL,
    [HomePageLayoutId] int  NOT NULL,
    [UserType] int  NOT NULL
);
GO

-- Creating table 'UserWidget'
CREATE TABLE [dbo].[UserWidget] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] int  NOT NULL,
    [WidgetId] int  NOT NULL,
    [ColumnNumber] int  NOT NULL,
    [ColumnOrder] int  NOT NULL
);
GO

-- Creating table 'Widget'
CREATE TABLE [dbo].[Widget] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [WidgetName] nvarchar(50)  NOT NULL,
    [HumanFriendly] nvarchar(150)  NULL
);
GO

-- Creating table 'Work'
CREATE TABLE [dbo].[Work] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [WorkTitle] nvarchar(200)  NOT NULL,
    [AddedDate] datetime  NULL,
    [Course] int  NOT NULL,
    [ProfessorId] int  NOT NULL,
    [Year] int  NOT NULL,
    [Summary] nvarchar(1000)  NOT NULL,
    [Questions] nvarchar(1000)  NULL,
    [Consultant] nvarchar(1000)  NULL,
    [GraphicalData] nvarchar(1000)  NOT NULL,
    [ProfessorId1] int  NOT NULL
);
GO

-- Creating table 'StudentEntitySet'
CREATE TABLE [dbo].[StudentEntitySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ProfessorEntitySet'
CREATE TABLE [dbo].[ProfessorEntitySet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Login] nvarchar(max)  NOT NULL,
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'HomePageLayout'
ALTER TABLE [dbo].[HomePageLayout]
ADD CONSTRAINT [PK_HomePageLayout]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [PK_User]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserWidget'
ALTER TABLE [dbo].[UserWidget]
ADD CONSTRAINT [PK_UserWidget]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Widget'
ALTER TABLE [dbo].[Widget]
ADD CONSTRAINT [PK_Widget]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Work'
ALTER TABLE [dbo].[Work]
ADD CONSTRAINT [PK_Work]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StudentEntitySet'
ALTER TABLE [dbo].[StudentEntitySet]
ADD CONSTRAINT [PK_StudentEntitySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProfessorEntitySet'
ALTER TABLE [dbo].[ProfessorEntitySet]
ADD CONSTRAINT [PK_ProfessorEntitySet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [HomePageLayoutId] in table 'User'
ALTER TABLE [dbo].[User]
ADD CONSTRAINT [FK_User_HomePageLayout]
    FOREIGN KEY ([HomePageLayoutId])
    REFERENCES [dbo].[HomePageLayout]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_User_HomePageLayout'
CREATE INDEX [IX_FK_User_HomePageLayout]
ON [dbo].[User]
    ([HomePageLayoutId]);
GO

-- Creating foreign key on [UserId] in table 'UserWidget'
ALTER TABLE [dbo].[UserWidget]
ADD CONSTRAINT [FK_UserWidget_User]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[User]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserWidget_User'
CREATE INDEX [IX_FK_UserWidget_User]
ON [dbo].[UserWidget]
    ([UserId]);
GO

-- Creating foreign key on [WidgetId] in table 'UserWidget'
ALTER TABLE [dbo].[UserWidget]
ADD CONSTRAINT [FK_UserWidget_Widget]
    FOREIGN KEY ([WidgetId])
    REFERENCES [dbo].[Widget]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserWidget_Widget'
CREATE INDEX [IX_FK_UserWidget_Widget]
ON [dbo].[UserWidget]
    ([WidgetId]);
GO

-- Creating foreign key on [ProfessorId] in table 'Work'
ALTER TABLE [dbo].[Work]
ADD CONSTRAINT [FK_Work_User]
    FOREIGN KEY ([ProfessorId])
    REFERENCES [dbo].[User]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Work_User'
CREATE INDEX [IX_FK_Work_User]
ON [dbo].[Work]
    ([ProfessorId]);
GO

-- Creating foreign key on [ProfessorId1] in table 'Work'
ALTER TABLE [dbo].[Work]
ADD CONSTRAINT [FK_WorkProfessor]
    FOREIGN KEY ([ProfessorId1])
    REFERENCES [dbo].[ProfessorEntitySet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkProfessor'
CREATE INDEX [IX_FK_WorkProfessor]
ON [dbo].[Work]
    ([ProfessorId1]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------